package com.constants;

public interface SQLConstants 
{
	//Person Bean Statements
	String createuser = "INSERT INTO videolibrary.customer (Fname,Lname,Address,City,State,ZipCode,Email,Password,Lastlogin) VALUES (?,?,?,?,?,?,?,?,?)";
	String getmemshipno="select memshipno from videolibrary.customer where Email=?";
	String insertuserintocustaccount = "INSERT INTO videolibrary.custaccount (Custtype,Memshipno,Movielimit,IssuedMovies,Balance,issuedmovieslist) VALUES (?,?,?,?,?,?)";
	String checkforduplicates="select * from videolibrary.customer where Email = ?";
	String validateuser = "select Password,Fname,Lname,ZipCode,Lastlogin,Memshipno,Address,City,State from videolibrary.customer where Email = ?";
	String updatelastlogin = "update videolibrary.customer set Lastlogin= ? where Email = ?";
	String updateAllUserData = "update videolibrary.customer a Join videolibrary.custaccount b on a.Memshipno = b.Memshipno set Fname = ?,Lname =?, Address = ?, City = ?, State = ?, ZipCode = ?, CustType=?,Password= ? where Email = ?";
	String selectusertodelete = "select Memshipno from videolibrary.customer where Email = ?";
	String checkforoutstandingmovies = "select Issuedmovies from videolibrary.custaccount where Memshipno=?";
	//String deleteuser= "delete from videolibrary.customer where Email=?";
	//String deleteuserfromcustaccount="delete from videolibrary.custaccount where Memshipno=?";
	String deleteuserfromorder = "delete from videolibrary.order where Userid=?";
	String checkordertablebeforedelete= "select * from videolibrary.order where Userid=?  and Status='P'";
	String deleteuserfromqueue = "delete from videolibrary.queue where Userid = ?";
	String verifyinqueuebeforedelete = "select * from videolibrary.queue where Userid = ?";
	String customerorder = "select movieid,rentdate,rentamount from videolibrary.order where Userid=? ORDER BY rentdate";
	String customerqueue="select Movieid from videolibrary.queue where Userid=?";
	String customermoviedetails="select a.moviename,a.catid,a.rating,a.moviebanner,b.catname,a.Rent from videolibrary.movies a Join videolibrary.category b on a.catid=b.catid where movieid=?";
	String getAllPersonData = "SELECT a.Memshipno,Fname,Lname,Address,City,State,Zipcode,Email,Lastlogin,Custtype,Movielimit,Issuedmovies,Issuedmovieslist,Balance FROM `videolibrary`.`customer` a Join videolibrary.custaccount b on a.Memshipno = b.Memshipno where Email = ?";
	String beforeinserttoqueue = "select * from videolibrary.queue where Userid=? and movieid=?";
	String inserttoQueue = "INSERT into videolibrary.queue (Userid,Movieid) values (?,?)"; 
	String deletefromQueue = "Delete from videolibrary.queue where Userid= ? and movieid= ?";
	String beforedeletefromQueue = "Select * from videolibrary.queue where Userid= ?  and movieid = ?";
	//End Person Bean Statements
	
	String GETPREMIUMMEMBERSLIST="SELECT c.Memshipno,c1.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,c1.Movielimit,c1.Issuedmovieslist,c.Lastlogin,c1.Balance from videolibrary.customer c,videolibrary.custaccount c1 where c.Memshipno=c1.Memshipno and c1.Custtype='P';";
	String GETSIMPLEMEMBERSLIST="SELECT c.Memshipno,c1.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,c1.Movielimit,c1.Issuedmovieslist,c.Lastlogin,c1.Balance from videolibrary.customer c,videolibrary.custaccount c1 where c.Memshipno=c1.Memshipno and c1.Custtype='S';";
	String GETALLMEMBERSLISTBYSTATE="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.State LIKE ? and c.Memshipno=p.Memshipno";
	String GETALLMEMBERSLISTBYCITY="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.City LIKE ? and c.Memshipno=p.Memshipno";
	String GETALLMEMBERSLISTBYZIP="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.Zipcode = ? and c.Memshipno=p.Memshipno";
	String GETALLMEMBERSBYFNAME="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.Fname LIKE ? and c.Memshipno=p.Memshipno";
	String GETALLMEMBERSBYLNAME="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.Lname LIKE ? and c.Memshipno=p.Memshipno";
	String GETALLMEMBERSBYID="SELECT c.Memshipno,p.Custtype,c.Fname,c.Lname,c.Address,c.City,c.State,c.Zipcode,c.Email,p.MovieLimit,p.Issuedmovieslist,c.Lastlogin,p.Balance from videolibrary.customer c,videolibrary.custaccount p where c.Memshipno = ?  and c.Memshipno=p.Memshipno";
	
	
	// SQL constants for Movie DAO starts here.
	String ADDNEWMOVIE = "INSERT INTO videolibrary.movies (Moviename,Moviebanner,Releasedate,Rent,Catid,Avlcopy,Rating,NumberRated,Totalcopy) VALUES(?,?,?,?,?,?,?,?,?)";
	String EDITMOVIE = "update videolibrary.movies set Moviename=?,Moviebanner=?,Releasedate=?,Rent=?,Catid=?,Avlcopy=?,Rating=?,NumberRated=?,Totalcopy=? WHERE Movieid=?;";
	String DELETEMOVIE = "Delete from movies where Movieid=?";
		
	String GETALLCATEGORYIDS = "SELECT CatId from videolibrary.category c;";
	String GETCATEGORYLIST = "SELECT CatName from videolibrary.category c where CatId=?;";
	
	String GETALLMOVIELIST = "SELECT Movieid,Moviename,Moviebanner,Releasedate,Rent,Catid,Rating,NumberRated,Avlcopy,Totalcopy from videolibrary.movies m;";
	String GETCATMOVIELIST = "SELECT Movieid, Moviename, Moviebanner, Releasedate, Rent, Rating, NumberRated, Avlcopy,Totalcopy from videolibrary.movies m where Catid=?;";
	
	String MOVIEAVAILABILITY = "SELECT Avlcopy FROM videolibrary.movies m where Movieid=?;";
	String PERSONELIGIBLE = "SELECT Movielimit FROM videolibrary.custaccount c where Memshipno=?;";
	
	String GETISSUEDMOVIES = "SELECT Issuedmovies, Issuedmovieslist, MovieLimit FROM videolibrary.custaccount c where Memshipno=?;";
	String SETISSUEDMOVIES = "update videolibrary.custaccount set Issuedmovies=?, Issuedmovieslist=?, MovieLimit=? where Memshipno=?;";
	
	String GETAVAILABLECOPIES = "SELECT Avlcopy FROM videolibrary.movies m where Movieid=?;";
	String SETAVAILABLECOPIES = "update videolibrary.movies set Avlcopy=? where Movieid=?;";
	
	String ADDORDER = "INSERT into videolibrary.order(Userid,Movieid,Rentamount,Status) values (?,?,?,'P');";
	String UPDATEORDER = "update videolibrary.order set Status='S' where Movieid=? AND Userid=? AND Status = 'P';";
	
	String GETRENTDATE = "select Rentdate from videolibrary.order where Userid=? AND Movieid=?;";
	
	String GETBALANCE = "select balance from videolibrary.custaccount where Memshipno =?;";
	String SETBALANCE = "update videolibrary.custaccount set balance=? where Memshipno =?;";
	
	String GENERATEBILL_SIMPLE = "select balance from videolibrary.custaccount where Memshipno =?;";
	String GENERATEBILL_PREMIUM = "SELECT Movieid, Rentdate FROM videolibrary.order WHERE YEAR(Rentdate) = YEAR(CURDATE()) AND MONTH(Rentdate) = MONTH(CURDATE()) AND Userid= ?;";

	String GETMOVIEBYID = "SELECT Moviename,Moviebanner,Releasedate,Rent,Catid,Rating,NumberRated,Avlcopy,Totalcopy from videolibrary.movies m where Movieid=?";
	
	String GETMOVIEBYNAME="SELECT m.Movieid,m.Moviename,m.Moviebanner,m.Releasedate,m.Rent,m.Catid,m.Avlcopy,ca.CatName FROM videolibrary.movies m,videolibrary.category ca  WHERE m.Moviename LIKE ? and m.Catid=ca.CatId";
	
	String GETMOVIEBYBANNER="SELECT m.Movieid,m.Moviename,m.Moviebanner,m.Releasedate,m.Rent,m.Catid,m.Avlcopy,ca.CatName FROM videolibrary.movies m,videolibrary.category ca WHERE m.Moviebanner LIKE ? and m.Catid=ca.CatId";
	
	
	
	String GETMOVIEBYRELEASEDATE="SELECT m.Movieid,m.Moviename,m.Moviebanner,m.Releasedate,m.Rent,m.Catid,m.Avlcopy,ca.CatName FROM videolibrary.movies m,videolibrary.category ca  WHERE m.Releasedate LIKE ? and m.Catid=ca.CatId";
	

	String GETMOVIEBYRENT="SELECT m.Movieid,m.Moviename,m.Moviebanner,m.Releasedate,m.Rent,m.Catid,m.Avlcopy,ca.CatName FROM videolibrary.movies m,videolibrary.category ca WHERE m.Rent=? and m.Catid=ca.CatId";	
	
	String GETMOVIEBYCATEGORY="SELECT m.Movieid,m.Moviename,m.Moviebanner,m.Releasedate,m.Rent,m.Catid,m.Avlcopy,ca.CatName FROM videolibrary.movies m,videolibrary.customer c,videolibrary.category ca WHERE ca.CatName=? and m.Catid=ca.CatId";
	
	
	
	String GETMOVIEPERSONBYSTATUS="SELECT o.Userid FROM videolibrary.order o WHERE o.Movieid= ? and o.status='P'";
	String GETPERSONNAME="SELECT c.Fname FROM videolibrary.customer c WHERE c.Memshipno=?";
	
	String ISSUEDMOVIESTOMEMBER="SELECT  c.Fname FROM videolibrary.movies m,videolibrary.order o,videolibrary.customer c WHERE m.Moviename LIKE ? and o.Userid=c.Memshipno and o.Movieid=m.Movieid ";
	String ISSUEDMOVIESTOMEMBERBANNER="SELECT c.Fname FROM videolibrary.movies m,videolibrary.order o,videolibrary.customer c WHERE m.Moviebanner LIKE ? and o.Userid=c.Memshipno and o.Movieid=m.Movieid ";
	String GETPERSONISSUEDAMOVIE="SELECT DISTINCT c1.Memshipno from videolibrary.custaccount c1,videolibrary.movies m where c1.Issuedmovieslist LIKE ?";
	String GETALLSTATES="SELECT State FROM statetable;";
	
	
	String DELETEMOVIECHECK1 = "select Orderid from videolibrary.order where Movieid=?;";
	String DELETEMOVIECHECK2 = "Delete from videolibrary.order where Movieid=?";
	
	String CHECKMOVIEQUEUE1 = "Select * from videolibrary.queue where movieid = ?";
	String CHECKMOVIEQUEUE2 = "Delete from videolibrary.queue where movieid= ?";
}
